#!/usr/bin/env python
# coding=utf-8
#----------------------------------------------------------------------------
# 文件名：      ali.py
# 说明：       mysql数据库操作类
# 作者：       fern
# 创建时间：    2015-3-21
# 版权：       (c) 2015 by fern
# 许可证：      apache license
#----------------------------------------------------------------------------
import mysqlDB
import hashlib,re,sys
from bs4 import BeautifulSoup
from  datetime  import  *
import gzip,urllib2,random,time
import urllib
import StringIO
import json
reload(sys)
sys.setdefaultencoding('utf-8')

class Ali:
    def __init__(self):
        self.db =mysqlDB.Mydb(host='localhost', dataBase='ali', user='root', passwd='123456')
        self.db.dbLoad()

    def siteurl(self):
        urls=[]
        urls.append('http://gzkalen.1688.com/page/offerlist.htm')
        urls.append('http://kybxgsjsp02.1688.com/page/offerlist.htm')
        urls.append('http://shop1400259471782.1688.com/page/offerlist.htm')
        urls.append('http://shop1405443437456.1688.com/page/offerlist.htm')
        pstr="?tradenumFilter=false&sampleFilter=false&mixFilter=false&privateFilter=false&mobileOfferFilter=%24mobileOfferFilter&groupFilter=false&sortType=tradenumdown&pageNum="
        for url in urls:
            cat=url
            pnum=1
            html=self.get_gzip(url).decode("GBK", 'ignore')
            soup = BeautifulSoup(html)
            if pnum==1 :
                allnum=self.get_page(soup)
            while pnum < allnum:
                if pnum > 1 :
                    urltemp=url+pstr+str(pnum)
                    cat=urltemp
                    html=self.get_gzip(url).decode("GBK", 'ignore')
                    soup = BeautifulSoup(html)
                list_html=soup.find(name="div",attrs={"class":"common-column-150"})
                product_list=list_html.find_all(name="li")
                for p in product_list:
                    try:
                        item={}
                        divhtml=p.find(name="div",attrs={"class":"image"})
                        item['image']=divhtml.a.img.get("data-lazy-load-src").strip()
                        item['href']=divhtml.a.get("href").strip()
                        item['title']=divhtml.a.get("title").strip()
                        item['cat']=cat
                        self.add_list(item)
                    except:
                        continue
                pnum=pnum+1
                # break

    def info(self):
        sql="SELECT * FROM  `product` WHERE 1;"
        query=self.db.query(sql)
        for row in query:
            bianhao=''
            # html=self.get_gzip('http://detail.1688.com/offer/40369568886.html').decode("GBK", 'ignore')
            html=self.get_gzip(row['url']).decode("GBK", 'ignore')
            soup = BeautifulSoup(html)
            #
            #抓取图片
            imghtml=soup.find(name="ul",attrs={"class":"nav nav-tabs fd-clr"})
            lis=imghtml.find_all(name="li",attrs={"class":"tab-trigger"})
            for li in lis:
                js=json.loads(li.get("data-imgs"))
                src=js['preview']
                osrc=js['original']
                imgsql="INSERT INTO `image`(`pid`, `src`, `osrc`) VALUES ('%s','%s','%s');" % (row['id'],src,osrc)
                print imgsql.encode('utf8')
                self.db.save(imgsql)
            #
            #抓取价格
            jiahtml=soup.find(name="tr",attrs={"class":"price"}).find_all(name="td",attrs={"class":re.compile('^ladder-\d+-')})
            for jia in jiahtml:
                js=jia.get("data-range")
                if js:
                    js=json.loads(js)
                    begin=js['begin']
                    end=js['end']
                    price=js['price']
                else:
                    begin=jia.parent.parent.find(name="tr",attrs={"class":"amount"}).find(name="span",attrs={"class":"value"}).string.encode('utf8')
                    begin=re.findall( r'\d+\Z' ,begin)[0]
                    end=''
                    prices=jia.find(name="div",attrs={"class":"price-original-sku"}).find_all(name="span",attrs={"class":"value"})
                    price=''
                    for pri in prices:
                        price+=pri.get_text()+' - '
                jiasql="INSERT INTO `jia`(`pid`, `end`, `begin`, `jia`) VALUES ('%s','%s','%s','%s');" % (row['id'],end,begin,price)
                print jiasql.encode('utf8')
                self.db.save(jiasql)
            #
            #获取编号这些信息
            detailhtml=soup.find(name="div",attrs={"id":"mod-detail-attributes"})
            detailtd=detailhtml.find_all(name="td")
            print detailtd
            for td in detailtd:
                if td.get('class')[0]=='de-feature':
                    name=td.string
                    if (name==u'生产编号') and bianhao=='':
                        bianhao='1'
                if td.get('class')[0]=='de-value':
                    val=td.string
                    print name.encode('utf8')
                    print val.encode('utf8')
                    if bianhao=='1':
                        bianhao=val
                    if name!='':
                        try:
                            val=self.db.escape_string(val.decode('utf8'))
                            name=self.db.escape_string(name.decode('utf8'))
                            attrsql="INSERT INTO `attrs`(`pid`, `name`, `val`) VALUES ('%s','%s','%s');" % (row['id'],name,val)
                            print attrsql
                            self.db.save(attrsql)
                        except:
                            attrsql=''
                    name=''
                    val=''
                    attrsql=''

            #获取产品的颜色价格信息
            try:
                yshtml=soup.find(name="table",attrs={"class":"table-sku"}).find_all(name="tr")
                for tr in yshtml:
                    src=osrc=yanse=jia=''
                    if tr.find(name="td",attrs={"class":"name"}).span.get('class'):
                        js=json.loads(tr.find(name="td",attrs={"class":"name"}).span.get('data-imgs'))
                        src=js['preview']
                        osrc=js['original']
                        yanse=tr.find(name="td",attrs={"class":"name"}).span.get('title')
                    else:
                        yanse=tr.find(name="td",attrs={"class":"name"}).span.get_text()
                    jia=tr.find(name="td",attrs={"class":"price"}).span.find(name="em",attrs={"class":"value"}).get_text()
                    yssql="INSERT INTO `yanse`(`pid`, `yanse`, `src`, `osrc`, `jia`) VALUES ('%s','%s','%s','%s','%s');" % (row['id'],yanse,src,osrc,jia)
                    print yssql.encode('utf8')
                    self.db.save(yssql)
                    yssql=''
            except:
                yssql=''
            if bianhao=='':
                bianhao='1'
            updatesql="UPDATE `product` SET `bianhao`='%s' WHERE `id`='%s';" % (bianhao,row['id'])
            print updatesql
            self.db.save(updatesql)
            updatesql=bianhao=''
            # break

    def close(self):
        self.db.dbClose()

    def get_page(self,soup):
        page_html=soup.find(name="em",attrs="page-count")
        return int(page_html.text)

    def add_list(self,item):
        sql="INSERT INTO `product` ( `title`, `image`, `url`, `cat`) VALUES ('%s', '%s', '%s', '%s');" % (item['title'],item['image'],item['href'],item['cat'])
        self.db.save(sql)
        print '添加成功'+item['href']+"\r\n"
        print item['cat']


    def get_gzip(self,url):
        header = {'Accept-Charset':'GBK,utf-8;q=0.7,*;q=0.3','User-Agent' : 'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.151 Safari/534.16'}
        request = urllib2.Request(url,headers=header)
        request.add_header('Accept-encoding', 'gzip')
        opener = urllib2.build_opener()
        f = opener.open(request)
        print f
        isGzip = f.headers.get('Content-Encoding')
        if isGzip :
            compresseddata = f.read()
            compressedstream = StringIO.StringIO(compresseddata)
            gzipper = gzip.GzipFile(fileobj=compressedstream)
            data = gzipper.read()
        else:
            data = f.read()
        return data

    def get_html(self,url):
        if url != None:
            #url="http://www.focalprice.com/iphone-5s/ca-001024.html"
            page = urllib.urlopen(url)
            html = page.read()
        else:
            html="""

            """
        return html

    def titlecn2en(self):
        sql="SELECT * FROM  `product` WHERE titleen=''"
        query=self.db.query(sql)
        for row in query:
            try:
                entitle=self.cn2en(row['title'])
                updatesql="UPDATE  `product` SET  `titleen` =  '%s' WHERE  `id` =%s;" % (self.db.escape_string(entitle),row['id'])
                print updatesql
                self.db.save(updatesql)
            except:
                continue

    def cn2en(self,str):
        str={"query":str.encode("utf8")}
        str=urllib.urlencode(str)
        url="http://apistore.baidu.com/microservice/translate?%s&from=zh&to=en" % (str)
        js=json.loads(self.get_html(url))
        return js['retData']['trans_result'][0]['dst']

if __name__ == '__main__':
    print "Prepare data. is come soon ..."
    ali=Ali()
    # ali.siteurl()
    # ali.titlecn2en()
    ali.info()
    ali.close()